package com.hk.kindheartedness.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

import androidx.annotation.Nullable;

import com.hk.kindheartedness.MainActivity;
import com.hk.kindheartedness.util.ToastUtil;

/**
 * @Author：alpha
 * @Create：2021/12/5/16:27
 * @Description：数据库连接操作类
 * @Version：1.0
 */
public class DBOpenHelper extends SQLiteOpenHelper {

//    定义创建表user（用户）的sql语句常量
    final String CREATE_TABLE_USER_SQL = "CREATE TABLE IF NOT EXISTS \"user\" (\n" +
        "  \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
        "  \"username\" TEXT NOT NULL,\n" +
        "  \"phone\" TEXT NOT NULL,\n" +
        "  \"password\" TEXT NOT NULL,\n" +
        "  \"type\" integer NOT NULL,\n" +
        "  \"medical_card_number\" TEXT ,\n" +
        "  \"sex\" integer NOT NULL,\n" +
        "  \"age\" INTEGER NOT NULL,\n" +
        "  \"id_number\" TEXT NOT NULL,\n" +
        "  \"address\" TEXT NOT NULL,\n" +
        "  \"doctor_id\" INTEGER,\n" +
        "  \"create_time\" TEXT NOT NULL,\n" +
        "  \"update_time\" TEXT NOT NULL\n" +
        ")";

//    创建表doctor_details（医生详情）的sql的语句
    final String CREATE_TABLE_DOCTOR_DETAILS_SQL = "CREATE TABLE IF NOT EXISTS \"doctor_details\" (\n" +
        "  \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
        "  \"username\" TEXT NOT NULL,\n" +
        "  \"position_id\" INTEGER NOT NULL,\n" +
        "  \"departments_id\" INTEGER NOT NULL,\n" +
        "  \"specialty\" TEXT,\n" +
        "  \"describe\" TEXT,\n" +
        "  \"create_time\" TEXT NOT NULL,\n" +
        "  \"update_time\" TEXT NOT NULL\n" +
        ")";

//    创建departments（科室）表SQL
    final String CREATE_TABLE_DEPARTMENTS_SQL = "CREATE TABLE IF NOT EXISTS \"departments\" (\n" +
        "  \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
        "  \"name\" TEXT NOT NULL,\n" +
        "  \"describe\" TEXT,\n" +
        "  \"departments_id\" integer NOT NULL DEFAULT 0,\n" +
        "  \"create_time\" TEXT NOT NULL,\n" +
        "  \"update_time\" TEXT NOT NULL\n" +
        ")";

//    创建position （职位）表SQL
    final String CREATE_TABLE_POSITION_SQL = "CREATE TABLE IF NOT EXISTS \"position\" (\n" +
        "  \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
        "  \"name\" TEXT NOT NULL,\n" +
        "  \"describe\" TEXT,\n" +
        "  \"create_time\" TEXT NOT NULL,\n" +
        "  \"update_time\" TEXT NOT NULL\n" +
        ")";

//    创建medicine（药物）表SQL
    final String CREATE_TABLE_MEDICINE_SQL = "CREATE TABLE IF NOT EXISTS \"medicine\" (\n" +
        "  \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
        "  \"user_id\" INTEGER NOT NULL,\n" +
        "  \"name\" TEXT NOT NULL,\n" +
        "  \"model\" TEXT,\n" +
        "  \"describe\" TEXT,\n" +
        "  \"unit_price\" REAL,\n" +
        "  \"total_price\" REAL,\n" +
        "  \"medicine_count\" integer,\n" +
        "  \"remaining_number\" INTEGER,\n" +
        "  \"create_time\" TEXT,\n" +
        "  \"update_time\" TEXT\n" +
        ")";

//    创建registration_setting（挂号设置）表SQL
    final String CREATE_TABLE_REGISTRATION_SETTING_SQL = "CREATE TABLE IF NOT EXISTS \"registration_setting\" (\n" +
        "  \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
        "  \"doctor_id\" INTEGER NOT NULL,\n" +
        "  \"registration_date\" TEXT NOT NULL,\n" +
        "  \"registration_time\" TEXT NOT NULL,\n" +
        "  \"count\" INTEGER,\n" +
        "  \"remaining_number\" integer,\n" +
        "  \"registration_money\" real,\n" +
        "  \"create_time\" TEXT NOT NULL,\n" +
        "  \"update_time\" TEXT NOT NULL\n" +
        ")";

//    创建registration_log（患者挂号记录）表SQL
    final String CREATE_TABLE_REGISTRATION_LOG_SQL = "CREATE TABLE IF NOT EXISTS \"registration_log\" (\n" +
            "  \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
            "  \"user_id\" INTEGER NOT NULL,\n" +
            "  \"registration_id\" INTEGER NOT NULL,\n" +
            "  \"subscribe_time\" TEXT,\n" +
            "  \"is_see_a_doctor\" integer NOT NULL DEFAULT 0,\n" +
            "  \"create_time\" TEXT NOT NULL,\n" +
            "  \"update_time\" TEXT NOT NULL\n" +
            ")";

//    创建case_history（病历记录）表SQL
    final String CREATE_TABLE_CASE_HISTORY_SQL = "CREATE TABLE IF NOT EXISTS \"case_history\" (\n" +
            "  \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
            "  \"username\" TEXT,\n" +
            "  \"user_number\" INTEGER,\n" +
            "  \"sex\" TEXT,\n" +
            "  \"age\" INTEGER,\n" +
            "  \"departments_name\" INTEGER,\n" +
            "  \"main_suit\" TEXT,\n" +
            "  \"phi\" TEXT,\n" +
            "  \"previous_history\" TEXT,\n" +
            "  \"drug_allergy_history\" TEXT,\n" +
            "  \"personal_and_family_history\" TEXT,\n" +
            "  \"health_checkup\" TEXT,\n" +
            "  \"auxiliary_examination\" TEXT,\n" +
            "  \"tentative_diagnosis\" TEXT,\n" +
            "  \"user_id\" INTEGER,\n" +
            "  \"doctor_id\" INTEGER,\n" +
            "  \"registration_log_id\" INTEGER,\n" +
            "  \"patient_name\" TEXT,\n" +
            "  \"create_time\" TEXT NOT NULL,\n" +
            "  \"update_time\" TEXT NOT NULL\n" +
            ")";

//    创建prescription (病历药物关联表)表SQL
    final String CREATE_TABLE_PRESCRIPTION_SQL = "CREATE TABLE IF NOT EXISTS \"prescription\" (\n" +
            "  \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
            "  \"case_history_id\" INTEGER,\n" +
            "  \"name\" TEXT,\n" +
            "  \"model\" TEXT,\n" +
            "  \"single_dose\" TEXT,\n" +
            "  \"usage\" TEXT,\n" +
            "  \"frequency\" TEXT,\n" +
            "  \"attention\" TEXT,\n" +
            "  \"create_time\" TEXT NOT NULL,\n" +
            "  \"update_time\" TEXT NOT NULL\n" +
            ")";

//    初始化表记录数
//    用户
    public final static String INSERT_USER_SQL = "INSERT INTO \"user\"(\"id\", \"username\", \"phone\", \"password\", \"type\", \"medical_card_number\", \"sex\", \"age\", \"id_number\", \"address\", \"doctor_id\", \"create_time\", \"update_time\") VALUES \n" +
        "(1, '王思维', '15829982210', '123456', 0, 202203052134, 0, 25, 610424199504290813, '成都武侯区簇桥街', NULL, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(2, '张凯文', '15829982211', '123456', 1, NULL, 0, 26, 610424199504290818, '成都武侯区簇桥街', 1, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(3, '思文', '15829982212', '123456', 2, NULL, 1, 22, 610424199504290820, '成都武侯区簇桥街', NULL, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(4, '周武汉', '15829982213', '123456', 3, NULL, 0, 28, 610424199504290821, '成都武侯区簇桥街', NULL, '2022-03-05 15:23:32', '2022-03-05 15:23:32');";

//    医生详情
    final static String INSERT_DOCTOR_DETAILS_SQL = "INSERT INTO \"doctor_details\"(\"id\", \"username\", \"position_id\", \"departments_id\", \"specialty\", \"describe\", \"create_time\", \"update_time\") VALUES (1, '张凯文', 3, 11, '擅长脑血管疾病（中风）、痴呆、睡眠障碍（失眠等）、神经变性疾病（帕金森、老年性痴呆等）、肌病及肌张力障碍性疾病（各种肌肉抽动）。', '中共党员，国内著名神经内科专家，主任医师。毕于华西医科大学医学系，现任成都市双楠医院神经内科主任，神经内科学科带头人。原四川省军区医院院长，成都军区医学高评委委员，成都军区神经病学分会委员，中华医学会神经病学分会肌张力障碍组委员，中华睡眠学会委员，中华康复医学会委员，四川省康复医学会常务理事。曾任四川省第八、九届政协委员。从事神经内科专业临床研究四十余年，积累了丰富的临床工作经验。对神经内科特别是脑血管疾病、神经变性疾病、肌病及肌张力障碍性疾病有着较深的造诣。曾主持过多项科研项目，与上海第二军医大学合作研究“变构蛇神经毒素”治疗“运动神经元病”，以及同西安医学院合作研究“跟腱反射时间测定”设立“坐骨神经痛”诊断的数字标准等多项科研项目。在国内各专科杂志上发表论文及学术文章逾46篇，《中华临床医学杂志》和《药物与临床杂志》编委，曾参加编写过《 A 型肉毒毒素治疗肌张力障碍》,1991年出版了2000万字的《腱反射学》个人专着。全军优秀共产党员，立三等功一次，先后二次获得成都军区优秀科技工作者称号，先后获得军队科技进步二等奖，三等奖及四等奖。擅长脑血管疾病（中风）、痴呆、睡眠障碍（失眠等）、神经变性疾病（帕金森、老年性痴呆等）、肌病及肌张力障碍性疾', '2022-03-05 15:23:32', '2022-03-05 15:23:32');\n";

//    科室
    final static String INSERT_DEPARTMENTS_SQL = "INSERT INTO \"departments\"(\"id\", \"name\", \"describe\", \"departments_id\", \"create_time\", \"update_time\") VALUES \n" +
        "(1, '内科', NULL, 0, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(2, '外科', NULL, 0, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(3, '儿科', NULL, 0, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(4, '口腔科', NULL, 0, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(5, '眼科', NULL, 0, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(6, '耳喉鼻科', NULL, 0, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(7, '皮肤美容科', NULL, 0, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(8, '呼吸内科', NULL, 1, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(9, '内分泌科', NULL, 1, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(10, '心血管内科', NULL, 1, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(11, '神经内科', '主要治疗肌肉/肢体神经/脊髓神经/脑部神经等,多名神经领域名家联合看诊,采用神经修复技术,促进神经功能重建。', 1, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(12, '消化内科', NULL, 1, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(13, '普通内科', NULL, 1, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(14, '血液科', NULL, 1, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(15, '普通外科', NULL, 2, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(16, '脑外科', NULL, 2, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(17, '神经外科', NULL, 2, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(18, '骨科', NULL, 2, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(19, '泌尿外科', NULL, 2, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(20, '肛肠科', NULL, 2, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(21, '普通儿科', NULL, 3, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(22, '新生儿科', NULL, 3, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(23, '儿保科', NULL, 3, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(24, '皮肤科', NULL, 7, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(25, '美容科', NULL, 7, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(26, '美容科', NULL, 7, '2022-03-05 15:23:32', '2022-03-05 15:23:32');\n";

//    职务
    final static String INSERT_POSITION_SQL = "INSERT INTO \"position\"(\"id\", \"name\", \"describe\", \"create_time\", \"update_time\") VALUES \n" +
        "(1, '主任医师', NULL, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(2, '副主任医师', NULL, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(3, '主治医师', NULL, '2022-03-05 15:23:32', '2022-03-05 15:23:32'),\n" +
        "(4, '医师', NULL, '2022-03-05 15:23:32', '2022-03-05 15:23:32');\n";

//    药物
    final static String INSERT_MEDICINE_SQL = "INSERT INTO \"medicine\"(\"id\", \"user_id\", \"name\", \"model\", \"describe\", \"unit_price\", \"total_price\", \"medicine_count\", \"remaining_number\", \"create_time\", \"update_time\") VALUES (1, 4, '谷维素', '10/mg', '本品为白色或类白色片。适用于神经官能症、经前期紧张综合征、更年期综合征的镇静助眠。', 10.0, 1000.0, 100, 100, '2022-03-05 15:23:32', '2022-03-05 15:23:32');\n";

//    挂号设置
//    final static String INSERT_REGISTRATION_SETTING_SQL = "INSERT INTO \"registration_setting\"(\"id\", \"doctor_id\", \"registration_date\", \"registration_time\", \"count\", \"remaining_number\", \"registration_money\", \"create_time\", \"update_time\") VALUES (1, 1, '2022-03-28','15:30', 100, 100, 10.0, '2022-03-05 15:23:32', '2022-03-05 15:23:32');\n";

//    挂号记录
//    final static String INSERT_REGISTRATION_LOG_SQL = "INSERT INTO \"registration_log\"(\"id\", \"user_id\", \"registration_id\", \"subscribe_time\", \"is_see_a_doctor\", \"create_time\", \"update_time\") VALUES (1, 1, 1, '2022-03-05 15:23:32', 0, '2022-03-05 15:23:32', '2022-03-05 15:23:32');\n";

    public DBOpenHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, null, version);


    }

    /**
     * 创建表
     * @param db 当前sqlite库
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.i("=======","onCreate =============");
//        执行SQL创建相关表
        db.execSQL(CREATE_TABLE_USER_SQL);
        db.execSQL(CREATE_TABLE_DOCTOR_DETAILS_SQL);
        db.execSQL(CREATE_TABLE_DEPARTMENTS_SQL);
        db.execSQL(CREATE_TABLE_POSITION_SQL);
        db.execSQL(CREATE_TABLE_MEDICINE_SQL);
        db.execSQL(CREATE_TABLE_REGISTRATION_SETTING_SQL);
        db.execSQL(CREATE_TABLE_REGISTRATION_LOG_SQL);
        db.execSQL(CREATE_TABLE_CASE_HISTORY_SQL);
        db.execSQL(CREATE_TABLE_PRESCRIPTION_SQL);

//        执行SQL新增基础数据
        db.execSQL(INSERT_USER_SQL);
        db.execSQL(INSERT_DOCTOR_DETAILS_SQL);
        db.execSQL(INSERT_DEPARTMENTS_SQL);
        db.execSQL(INSERT_POSITION_SQL);
        db.execSQL(INSERT_MEDICINE_SQL);
//        db.execSQL(INSERT_REGISTRATION_SETTING_SQL);
//        db.execSQL(INSERT_REGISTRATION_LOG_SQL);

    }

    /**
     * 版本更新
     * @param db 当前的数据库
     * @param oldVersion 旧版本
     * @param newVersion 新版本
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i("仁爱","---版本更新"+oldVersion+"--->"+newVersion);

    }
}
